Connecting to Oracle Server via Different programming Languages

In order to store or access the data inside an Oracle database, you first need to connect to the Oracle database server. We will show you the sample codes to connect your Oracle via Java and Python.

Connecting via Java

Before you start, you need to add Oracle connector package to your Java project. The download link for the Oracle connector is https://www.oracle.com/database/technologies/jdbc-drivers-12c-downloads.html

package oracle_connection;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class OracleConnection {

    // https://www.oracle.com/database/technologies/jdbc-drivers-12c-downloads.html
    public static void main(String[] args) {
        Connection conn = null;
        String dbServer = "oracle-xxxxx-0.cloudclusters.net"; // change it to your database server name 
        int dbPort = 4229; // change it to your database server port
        String userName = "your database user name";
        String password = "your database password";
        String url = String.format("jdbc:oracle:thin:@%s:%d:xe", dbServer, dbPort);
        try {

            Class.forName("oracle.jdbc.driver.OracleDriver");  
            conn = DriverManager.getConnection(url, userName, password);
            Statement stmt = conn.createStatement();

            // create table
            String sql = "create table teacher(NO char(20), name varchar(20),primary key(NO))";
            int result = stmt.executeUpdate(sql);

            // insert data
            if (result != -1) {
                sql = "insert into teacher(NO,name) values('202001','ben')";
                result = stmt.executeUpdate(sql);
                sql = "insert into teacher(NO,name) values('202002','ethan')";
                result = stmt.executeUpdate(sql);
            }

            // query data
            sql = "select * from teacher";
            ResultSet rs = stmt.executeQuery(sql);
            System.out.println("No.\tName");
            while (rs.next()) {
                System.out.println(rs.getString(1) + "\t" + rs.getString(2));
            }
        } catch (SQLException e) {
            System.out.println("Oracle Server connection had an exception");
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

}

Connecting via Python

Install cx_Oracle

pip install cx_Oracle

Connect to your server

import cx_Oracle


class OracleConnection(object):
    def __init__(self):
        self.SERVER = 'connect server'
        self.PORT = 'server port' # your port
        self.USER = 'login user'
        self.PASSWORD = 'user password'
        self.DATABASE = 'XE'

    def connect_oracle(self):
        conn = cx_Oracle.connect(self.USER, self.PASSWORD,
        '{}:{}/{}'.format(self.SERVER, self.PORT, self.DATABASE),
        cx_Oracle.SYSDBA)
        return conn

    def operate_database(self):
        # example select login user
        connect = self.connect_oracle()
        curs = connect.cursor()
        sql = "select tablespace_name from dba_tablespaces"
        curs.execute(sql)
        row = curs.fetchone()
        print(row[0])
        curs.close()
        connect.close()


if _name_ == '__main__':
    OracleConnection().operate_database()